package com.hanma.dao.impl;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.hanma.bean.Category;
import com.hanma.bean.News;
import com.hanma.bean.User;
import com.hanma.dao.NewsDao;
import com.hanma.util.DBUtil;

public class NewsDaoImpl implements NewsDao{

	@Override
	public int insert(News news) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql="insert into news(title,cate_id,se_cate_id,content)  values(?,?,?,?)"; 
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, news.getTitle());
			ps.setInt(2, news.getCateId());
			ps.setInt(3, news.getSeCateId());
			ps.setString(4, news.getContent());
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public int update(News news) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql="update news set title=?,cate_id=?,se_cate_id=?,content=? where id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, news.getTitle());
			ps.setInt(2, news.getCateId());
			ps.setInt(3, news.getSeCateId());
			ps.setString(4, news.getContent());
			ps.setInt(5, news.getId());
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public int delete(Integer id) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql="update news set status=0 where id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public News find(Integer id) {
		Connection conn = DBUtil.open();
		String sql="select * from news where id=? && status=1";
		PreparedStatement ps = null;
		ResultSet rs = null;
		News news = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				String title=rs.getString("title");
				Integer cate_id=rs.getInt("cate_id");
				Integer se_cate_id=rs.getInt("se_cate_id");
				String content=rs.getString("content");
				Integer comment=rs.getInt("comment");
				Integer recommend=rs.getInt("recommend");
				news = new News(id,title,cate_id,se_cate_id,content,comment,recommend);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return news;
	}

	@Override
	public int commentClick(Integer id) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql = "UPDATE news SET comment = comment + 1 WHERE id = ?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public int incrementNewsClickCount(Integer id) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql = "UPDATE news SET recommend = recommend + 1 WHERE id = ?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public List<News> findAllSearch(String search) {
		Connection conn = DBUtil.open();
		String sql = "select * from news where status=1 and title like ? ORDER BY id asc ";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<News> newsList = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, '%'+search+'%');
			rs = ps.executeQuery();
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String title=rs.getString("title");
				String content=rs.getString("content");
				Integer comment=rs.getInt("comment");
				Integer recommend=rs.getInt("recommend");
				News news = new News(id,title,content,comment,recommend);
				newsList.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return newsList;
	}

	@Override
	public List<News> findAllSearch(String search, Integer start, Integer size) {
		Connection conn = DBUtil.open();
		String sql="select * from news where status=1 and title like ? ORDER BY id asc limit ?,?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<News> newsList = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, '%'+search+'%');
			ps.setInt(2, start);
			ps.setInt(3, size);
			rs = ps.executeQuery();
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String title=rs.getString("title");
				String content=rs.getString("content");
				Integer comment=rs.getInt("comment");
				Integer recommend=rs.getInt("recommend");
				News news = new News(id,title,content,comment,recommend);
				newsList.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return newsList;
	}

	@Override
	public List<News> findAllOne(Integer cate_id) {
		Connection conn = DBUtil.open();
		String sql="select * from news where cate_id=? && status=1";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<News> newsList = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, cate_id);
			rs = ps.executeQuery();
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String title=rs.getString("title");
				String content=rs.getString("content");
				Integer comment=rs.getInt("comment");
				Integer recommend=rs.getInt("recommend");
				News news = new News(id,title,content,comment,recommend);
				newsList.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return newsList;
	}

	@Override
	public List<News> findAllOne(Integer cate_id,Integer start, Integer size) {
		Connection conn = DBUtil.open();
		String sql="select * from news where cate_id=? && status=1 order by id limit ?,?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<News> newsList = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, cate_id);
			ps.setInt(2, start);
			ps.setInt(3, size);
			rs = ps.executeQuery();
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String title=rs.getString("title");
				String content=rs.getString("content");
				Integer comment=rs.getInt("comment");
				Integer recommend=rs.getInt("recommend");
				News news = new News(id,title,content,comment,recommend);
				newsList.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return newsList;
	}

	@Override
	public List<News> findAllTwo(Integer se_cate_id) {
		Connection conn = DBUtil.open();
		String sql="select * from news where se_cate_id=? && status=1";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<News> newsList = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, se_cate_id);
			rs = ps.executeQuery();
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String title=rs.getString("title");
				String content=rs.getString("content");
				Integer cate_id=rs.getInt("cate_id");
				Integer comment=rs.getInt("comment");
				Integer recommend=rs.getInt("recommend");
				News news = new News(id,title,cate_id,se_cate_id,content,comment,recommend);
				newsList.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return newsList;
	}

	@Override
	public List<News> findAllTwo(Integer se_cate_id, Integer start, Integer size) {
		Connection conn = DBUtil.open();
		String sql="select * from news where se_cate_id=? && status=1 order by id limit ?,?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<News> newsList = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, se_cate_id);
			ps.setInt(2, start);
			ps.setInt(3, size);
			rs = ps.executeQuery();
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String title=rs.getString("title");
				String content=rs.getString("content");
				Integer cate_id=rs.getInt("cate_id");
				Integer comment=rs.getInt("comment");
				Integer recommend=rs.getInt("recommend");
				News news = new News(id,title,cate_id,se_cate_id,content,comment,recommend);
				newsList.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return newsList;
	}

	@Override
	public List<News> findAllRec() {
		Connection conn = DBUtil.open();
		String sql="select * from news where status=1 order by recommend desc";
		Statement str = null;
		ResultSet rs = null;
		List<News> newsList = new ArrayList<>();
		try {
			str = conn.createStatement();
			rs = str.executeQuery(sql);

			while(rs.next()) {
				Integer id=rs.getInt("id");
				String title=rs.getString("title");
				String content=rs.getString("content");
				Integer comment=rs.getInt("comment");
				Integer recommend=rs.getInt("recommend");
				News news = new News(id,title,content,comment,recommend);
				newsList.add(news);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, str, conn);
		}
		return newsList;
	}

	@Override
	public List<News> findAllRec(Integer start, Integer size) {
		Connection conn = DBUtil.open();
		String sql="select * from news where status=1 order by recommend desc limit ?,?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<News> newsList = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, size);
			rs = ps.executeQuery();
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String title=rs.getString("title");
				String content=rs.getString("content");
				Integer comment=rs.getInt("comment");
				Integer recommend=rs.getInt("recommend");
				News news = new News(id,title,content,comment,recommend);
				newsList.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return newsList;
	}

	@Override
	public List<News> findAllByState() {
		Connection conn = DBUtil.open();
		String sql="select * from news where status = 1";
		Statement str = null;
		ResultSet rs = null;
		List<News> newsList = new ArrayList<>();
		try {
			str = conn.createStatement();
			rs = str.executeQuery(sql);

			while(rs.next()) {
				Integer id=rs.getInt("id");
				String title=rs.getString("title");
				Integer cate_id=rs.getInt("cate_id");
				Integer se_cate_id=rs.getInt("se_cate_id");
				News news = new News(id,title,cate_id,se_cate_id);
				newsList.add(news);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, str, conn);
		}
		return newsList;
	}
	@Override
	public List<News> findAllByState(Integer start, Integer size) {
		Connection conn = DBUtil.open();
		String sql="select * from news where status = 1 order by id limit ?,?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<News> newsList = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, size);
			rs = ps.executeQuery();
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String title=rs.getString("title");
				Integer cate_id=rs.getInt("cate_id");
				Integer se_cate_id=rs.getInt("se_cate_id");
				News news = new News(id,title,cate_id,se_cate_id);
				newsList.add(news);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return newsList;
	}

}
